﻿using Microsoft.Data.Sqlite;

namespace MauiSampleDemo.Samples.DatabaseOperate
{
    public class DatabaseService
    {
        public async Task<bool> InitAsync()
        {
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();
            try
            {
                var createTableCmd = connection.CreateCommand();
                createTableCmd.CommandText = @"
                                                CREATE TABLE IF NOT EXISTS TestTable (
                                                    ID INTEGER PRIMARY KEY AUTOINCREMENT,
                                                    Name TEXT NOT NULL,
                                                    Description TEXT NOT NULL
                                                );";
                await createTableCmd.ExecuteNonQueryAsync();
                return true;
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine($"Init error, Msg: ${e.Message}");
            }
            return false;
        }

        public async Task<bool> InsertAsync()
        {
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();
            try
            {
                var createTableCmd = connection.CreateCommand();
                createTableCmd.CommandText = @"INSERT INTO TestTable (Name, Description) VALUES('zhangsan','zs');";
                await createTableCmd.ExecuteNonQueryAsync();
                return true;
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine($"Init error, Msg: ${e.Message}");
            }
            return false;
        }

        public async Task<List<UserInfo>> SelectAsync()
        {
            List<UserInfo> users = new List<UserInfo>();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();
            try
            {
                var selectCmd = connection.CreateCommand();
                selectCmd.CommandText = @"SELECT * FROM TestTable;";
                await using var reader = await selectCmd.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    users.Add(new UserInfo
                    {
                        Id = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        Description = reader.GetString(2)
                    });
                }
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine($"Init error, Msg: ${e.Message}");
            }
            return users;
        }
    }

    public class UserInfo
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
    }
}
